package com.suypower.carManage.json;

import com.jarp.db.DAO;
import com.opensymphony.xwork2.ActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.suypower.carManage.VO.CarVO;
import com.suypower.carManage.VO.CostVO;
import com.suypower.carManage.comm.ConstUtil;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import net.sf.json.JSONArray;

import javax.servlet.ServletException;
import java.io.*;
import java.util.*;

public class CostJson extends ActionSupport {

    private String start_time;
    private String end_time;
    private String type;
    private String car;
    private String department;
    private String attribute;
    private String start = "1";
    private String limit = "20";
    private String _dc;
    InputStream excelStream;
    ActionContext cxt = ActionContext.getContext();
    Map<String, Object> session = cxt.getSession();
    String userid = (String) session.get("userid");
    private String excelTitle;
    private String title = "费用列表";

    public String getStart_time() {
        return start_time;
    }

    public void setStart_time(String startTime) {
        start_time = startTime;
    }

    public String getEnd_time() {
        return end_time;
    }

    public void setEnd_time(String endTime) {
        end_time = endTime;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getCar() {
        return car;
    }

    public void setCar(String car) {
        this.car = car;
    }

    public String getStart() {
        return start;
    }

    public void setStart(String start) {
        this.start = start;
    }

    public String getLimit() {
        return limit;
    }

    public void setLimit(String limit) {
        this.limit = limit;
    }

    public String get_dc() {
        return _dc;
    }

    public String getAttribute() {
        return attribute;
    }

    public void setAttribute(String attribute) {
        this.attribute = attribute;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public void set_dc(String dc) {
        _dc = dc;
    }

    public String execute() {
        int iStart = Integer.parseInt(start);
        int iPageSize = Integer.parseInt(limit);
        int iPage = iStart / iPageSize + 1;
        DAO dao = new DAO();
        String Local_car = "";
        if (car != null && !car.equals("")) {
            String[] cares = car.split(",");

            for (String str : cares) {
                Local_car += "'" + str + "',";
            }
            Local_car = Local_car.substring(0, Local_car.length() - 1);
        }
        ArrayList<CostVO> list = new ArrayList<CostVO>();
        if (type != null && !type.equals("")) {
            String[] select = type.split(",");
            StringBuffer sql = new StringBuffer();
            for (int i = 0; i < select.length; i++) {
                if (i == 0) {

                } else {
                    sql.append("  UNION ALL  ");
                }
                if (select[i].equals(ConstUtil.COSTOFREPAIR)) {
                    sql.append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t1.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t1.repair_date<='" + end_time
                                    + "' AND t1.repair_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFMAINTAIN)) {
                    sql.append("SELECT t1.car_code,t1.maintain_date AS DATE,t1.id AS code,'车辆保养费用' AS name,t1.remark,t1.price AS cost FROM tb_maintain t1");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t1.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t1.maintain_date<='" + end_time
                                    + "' AND t1.maintain_date>='" + start_time + "')");
                        }
                    }
                }

                if (select[i].equals(ConstUtil.COSTOFTYRE)) {
                    sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t4.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t4.change_date<='" + end_time
                                    + "' AND t4.change_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFREFUEL)) {
                    sql.append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t2.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t2.ref_date<='" + end_time
                                    + "' AND t2.ref_date>='" + start_time + "')");
                        }
                    }

                    sql.append(" UNION ALL  ");
                    sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t5.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t5.recharge_date<='" + end_time
                                    + "' AND t5.recharge_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFEE)) {
                    sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t3.car_code in (" + Local_car + ") ");
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFBRIDGE)) {
                    sql.append("SELECT t6.car_code,t6.fee_date AS DATE,t6.id AS code,a.name,t6.remark,t6.cost FROM tb_tolls t6 ");
                    sql.append(" LEFT JOIN tb_metadata a ON t6.tolls_type = a.value ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    sql.append("and a.code = 'tolls' ");
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t6.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t6.fee_date<='" + end_time
                                    + "' AND t6.fee_date>='" + start_time + "')");
                        }
                    }
                }

            }
            String sql2 = sql.toString();
            sql.append(" ORDER BY 3 ASC ");
            System.out.println(sql.toString());
            try {
                Vector v = dao.getDataSet(ConstUtil.DBNAME, sql.toString(),
                        iPage, iPageSize);
                if (v != null) {
                    double sum = 0;
                    for (Object obj : v) {
                        Hashtable hash = (Hashtable) obj;
                        CostVO cost = new CostVO();
                        cost.setCar_code((String) hash.get("CAR_CODE"));
                        cost.setDate((String) hash.get("DATE"));
                        cost.setCode((String) hash.get("CODE"));
                        cost.setName((String) hash.get("NAME"));
                        cost.setRemark((String) hash.get("REMARK"));
                        cost.setCost((String) hash.get("COST"));
                        sum += convertToDouble(cost.getCost());
                        list.add(cost);
                    }
                    CostVO cost = new CostVO();
                    cost.setCar_code("当前页总额");
                    cost.setCost(sum + "");
                    list.add(cost);
                    CostVO costTotla = new CostVO();
                    StringBuffer sql_sum = new StringBuffer();
                    sql_sum.append("SELECT sum(t.cost) FROM (");
                    sql_sum.append(sql2);
                    sql_sum.append(" ) t");
                    String sum_val = dao.getDataValue(ConstUtil.DBNAME, sql_sum.toString());
                    costTotla.setCar_code("总额");
                    costTotla.setCost(sum_val);
                    list.add(costTotla);
                }
                String totalCountSQL = "select count(1) CNT from (" + sql2
                        + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS,
                        totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put(
                        "data",
                        "{totalCount:" + totalCount + ",data:"
                        + json.toString() + "}");
            } catch (ServletException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } else {
            // 没有选择具体的费用项目
            StringBuffer sql = new StringBuffer();
            sql.append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t1.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t1.repair_date<='" + end_time
                            + "' AND t1.repair_date>='" + start_time + "')");
                }
            }
            sql.append("  UNION ALL  ");
            sql.append("SELECT t1.car_code,t1.maintain_date AS DATE,t1.id AS code,'车辆保养费用' AS name,t1.remark,t1.price AS cost FROM tb_maintain t1");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t1.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t1.maintain_date<='" + end_time
                            + "' AND t1.maintain_date>='" + start_time + "')");
                }
            }
            sql.append("  UNION ALL  ");
            sql.append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t2.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t2.ref_date<='" + end_time
                            + "' AND t2.ref_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t3.car_code in (" + Local_car + ") ");
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t4.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t4.change_date<='" + end_time
                            + "' AND t4.change_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t5.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t5.recharge_date<='" + end_time
                            + "' AND t5.recharge_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t6.car_code,t6.fee_date AS DATE,t6.id AS code,a.name,t6.remark,t6.cost FROM tb_tolls t6 ");
            sql.append(" LEFT JOIN tb_metadata a ON t6.tolls_type = a.value ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            sql.append("and a.code = 'tolls' ");
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t6.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t6.fee_date<='" + end_time
                            + "' AND t6.fee_date>='" + start_time + "')");
                }
            }

            String sql2 = sql.toString();
            sql.append(" ORDER BY 3 ASC ");
            try {
                Vector v = dao.getDataSet(ConstUtil.DBNAME, sql.toString(),
                        iPage, iPageSize);
                if (v != null) {
                    double sum = 0;
                    for (Object obj : v) {
                        Hashtable hash = (Hashtable) obj;
                        CostVO cost = new CostVO();
                        cost.setCar_code((String) hash.get("CAR_CODE"));
                        cost.setDate((String) hash.get("DATE"));
                        cost.setCode((String) hash.get("CODE"));
                        cost.setName((String) hash.get("NAME"));
                        cost.setRemark((String) hash.get("REMARK"));
                        cost.setCost((String) hash.get("COST"));
                        sum += convertToDouble(cost.getCost());
                        list.add(cost);
                    }
                    CostVO cost = new CostVO();
                    cost.setCar_code("当前页总额");
                    cost.setCost(sum + "");
                    list.add(cost);
                    CostVO costTotla = new CostVO();
                    StringBuffer sql_sum = new StringBuffer();
                    sql_sum.append("SELECT sum(t.cost) FROM (");
                    sql_sum.append(sql2);
                    sql_sum.append(" ) t");
                    String sum_val = dao.getDataValue(ConstUtil.DBNAME, sql_sum.toString());
                    costTotla.setCar_code("总额");
                    costTotla.setCost(sum_val);
                    list.add(costTotla);
                }
                String totalCountSQL = "select count(1) CNT from (" + sql2
                        + " ) zz ";
                String totalCount = dao.getDataValue(ConstUtil.DBNAMEODS,
                        totalCountSQL);
                JSONArray json = JSONArray.fromObject(list);
                ActionContext.getContext().put(
                        "data",
                        "{totalCount:" + totalCount + ",data:"
                        + json.toString() + "}");
            } catch (ServletException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

        }
        return this.SUCCESS;
    }

    public StringBuffer getSql() {
        StringBuffer sql = new StringBuffer();
        String Local_car = "";
        if (car != null && !car.equals("")) {
            String[] cares = car.split(",");

            for (String str : cares) {
                Local_car += "'" + str + "',";
            }
            Local_car = Local_car.substring(0, Local_car.length() - 1);
        }
        if (type != null && !type.equals("")) {
            String[] select = type.split(",");
            for (int i = 0; i < select.length; i++) {
                if (i == 0) {

                } else {
                    sql.append("  UNION ALL  ");
                }
                if (select[i].equals(ConstUtil.COSTOFREPAIR)) {
                    sql.append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t1.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t1.repair_date<='" + end_time
                                    + "' AND t1.repair_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFMAINTAIN)) {
                    sql.append("SELECT t1.car_code,t1.maintain_date AS DATE,t1.id AS code,'车辆保养费用' AS name,t1.remark,t1.price AS cost FROM tb_maintain t1");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t1.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t1.maintain_date<='" + end_time
                                    + "' AND t1.maintain_date>='" + start_time + "')");
                        }
                    }
                }

                if (select[i].equals(ConstUtil.COSTOFTYRE)) {
                    sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t4.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t4.change_date<='" + end_time
                                    + "' AND t4.change_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFREFUEL)) {
                    sql.append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t2.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t2.ref_date<='" + end_time
                                    + "' AND t2.ref_date>='" + start_time + "')");
                        }
                    }

                    sql.append(" UNION ALL  ");
                    sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t5.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t5.recharge_date<='" + end_time
                                    + "' AND t5.recharge_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFEE)) {
                    sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t3.car_code in (" + Local_car + ") ");
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFBRIDGE)) {
                    sql.append("SELECT t6.car_code,t6.fee_date AS DATE,t6.id AS code,a.name,t6.remark,t6.cost FROM tb_tolls t6 ");
                    sql.append(" LEFT JOIN tb_metadata a ON t6.tolls_type = a.value ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    sql.append("and a.code = 'tolls' ");
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t6.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t6.fee_date<='" + end_time
                                    + "' AND t6.fee_date>='" + start_time + "')");
                        }
                    }
                }

            }
        } else {
            sql.append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t1.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t1.repair_date<='" + end_time
                            + "' AND t1.repair_date>='" + start_time + "')");
                }
            }
            sql.append("  UNION ALL  ");
            sql.append("SELECT t1.car_code,t1.maintain_date AS DATE,t1.id AS code,'车辆保养费用' AS name,t1.remark,t1.price AS cost FROM tb_maintain t1");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t1.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t1.maintain_date<='" + end_time
                            + "' AND t1.maintain_date>='" + start_time + "')");
                }
            }
            sql.append("  UNION ALL  ");
            sql.append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t2.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t2.ref_date<='" + end_time
                            + "' AND t2.ref_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t3.car_code in (" + Local_car + ") ");
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t4.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t4.change_date<='" + end_time
                            + "' AND t4.change_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t5.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t5.recharge_date<='" + end_time
                            + "' AND t5.recharge_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t6.car_code,t6.fee_date AS DATE,t6.id AS code,a.name,t6.remark,t6.cost FROM tb_tolls t6 ");
            sql.append(" LEFT JOIN tb_metadata a ON t6.tolls_type = a.value ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t6.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            sql.append("and a.code = 'tolls' ");
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t6.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t6.fee_date<='" + end_time
                            + "' AND t6.fee_date>='" + start_time + "')");
                }
            }
        }
        sql.append(" ORDER BY 3 ASC ");
        return sql;
    }

    public double convertToDouble(String value) {
        double b = 0;
        if (value == null || value.equals("")) {

        } else {
            try {
                b = Double.valueOf(value);
            } catch (Exception e) {
                b = 0;
            }
        }
        return b;
    }

    public StringBuffer getSql2() {
        String Local_car = "";
        if (car != null && !car.equals("")) {
            String[] cares = car.split(",");

            for (String str : cares) {
                Local_car += "'" + str + "',";
            }
            Local_car = Local_car.substring(0, Local_car.length() - 1);
        }
        StringBuffer sql = new StringBuffer();
        if (type != null && !type.equals("")) {
            String[] select = type.split(",");
            sql = new StringBuffer();
            for (int i = 0; i < select.length; i++) {
                if (i == 0) {

                } else {
                    sql.append("  UNION ALL  ");
                }
                if (select[i].equals(ConstUtil.COSTOFREPAIR)) {
                    sql.append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t1.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t1.repair_date<='" + end_time
                                    + "' AND t1.repair_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFREFUEL)) {
                    sql.append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t2.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t2.ref_date<='" + end_time
                                    + "' AND t2.ref_date>='" + start_time + "')");
                        }
                    }

                    sql.append(" UNION ALL  ");
                    sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t5.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t5.recharge_date<='" + end_time
                                    + "' AND t5.recharge_date>='" + start_time + "')");
                        }
                    }
                }
                if (select[i].equals(ConstUtil.COSTOFEE)) {
                    sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t3.car_code in (" + Local_car + ") ");
                    }

                    sql.append(" UNION ALL  ");
                    sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
                    if (department != null && !department.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.department='" + department + "' ");
                    } else if (attribute != null && !attribute.equals("")) {
                        sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                        sql.append(" WHERE b.attributes='" + attribute + "' ");
                    } else {
                        sql.append(" where 1=1 ");
                    }
                    if (Local_car != null && !Local_car.equals("")) {
                        sql.append(" and t4.car_code in (" + Local_car + ")");
                    }
                    if (start_time != null && !start_time.equals("")) {
                        if (end_time != null && !end_time.equals("")) {
                            sql.append(" AND (t4.change_date<='" + end_time
                                    + "' AND t4.change_date>='" + start_time + "')");
                        }
                    }
                }
            }
            String sql2 = sql.toString();

        } else {
            // 没有选择具体的费用项目
            sql = new StringBuffer();
            sql
                    .append("SELECT t1.car_code,t1.repair_date AS DATE,t1.id AS code,'车辆维修费用' AS name,t1.remark,t1.repair_price AS cost FROM tb_repair t1");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t1.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t1.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t1.repair_date<='" + end_time
                            + "' AND t1.repair_date>='" + start_time + "')");
                }
            }
            sql.append("  UNION ALL  ");
            sql
                    .append("SELECT t2.car_code,t2.ref_date AS DATE,t2.id AS code,'加油费用' AS name,t2.remark,t2.money AS cost FROM tb_refuel t2 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t2.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t2.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t2.ref_date<='" + end_time
                            + "' AND t2.ref_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t3.car_code,'' AS DATE,t3.id AS code,'规费及其他费用' AS name,t3.remark,t3.money as cost FROM tb_fee t3 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t3.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t3.car_code in (" + Local_car + ") ");
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t4.car_code,t4.change_date AS DATE,t4.id AS code,'轮胎更换费用' AS name,t4.remark,t4.price AS cost FROM tb_tyre t4 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t4.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t4.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t4.change_date<='" + end_time
                            + "' AND t4.change_date>='" + start_time + "')");
                }
            }
            sql.append(" UNION ALL  ");
            sql.append("SELECT t5.car_code,t5.recharge_date AS DATE,t5.id AS code,'加油卡充值记录' AS name,t5.remark,t5.recharge_money AS cost FROM tb_refuel_card_recharge t5 ");
            if (department != null && !department.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.department='" + department + "' ");
            } else if (attribute != null && !attribute.equals("")) {
                sql.append(" LEFT JOIN tb_car b ON t5.car_code=b.code ");
                sql.append(" WHERE b.attributes='" + attribute + "' ");
            } else {
                sql.append(" where 1=1 ");
            }
            if (Local_car != null && !Local_car.equals("")) {
                sql.append(" and t5.car_code in (" + Local_car + ")");
            }
            if (start_time != null && !start_time.equals("")) {
                if (end_time != null && !end_time.equals("")) {
                    sql.append(" AND (t5.recharge_date<='" + end_time
                            + "' AND t5.recharge_date>='" + start_time + "')");
                }
            }
            String sql2 = sql.toString();

        }
        return sql;
    }

    public String getExcel() throws UnsupportedEncodingException, IOException, WriteException {
        excelStream = this.getExcelInputStream();
        return "excel";
    }

    public InputStream getExcelInputStream() throws IOException, WriteException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        create(out);
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void create(OutputStream out) throws IOException, WriteException {
        DAO dao = new DAO();
        excelTitle = title + ".xls";
        excelTitle = new String(excelTitle.getBytes(), "ISO8859-1");
        List<String> count_name = new ArrayList<String>();
        //用来记录具体的列名
        count_name.add("车牌号码");
        count_name.add("费用时间");
        count_name.add("费用项目编号");
        count_name.add("费用项目名称");
        count_name.add("费用摘要");
        count_name.add("费用金额");
        WritableWorkbook book = Workbook.createWorkbook(out);
        WritableSheet sheet = book.createSheet(title, 0);
        sheet.setRowView(0, 810);//设置大标题格式
        for (int i = 2; i < 100; i++) {
            sheet.setRowView(i, 320);
        }
        sheet.setColumnView(0, 18);
        sheet.setColumnView(1, 12);//设置第一行的宽度  //注意宽度和高度的单位不相同
        sheet.setColumnView(2, 30);
        sheet.setColumnView(3, 30);
        sheet.setColumnView(4, 30);
        sheet.setColumnView(5, 17);
        sheet.setColumnView(6, 17);
        sheet.setColumnView(7, 17);
        sheet.setColumnView(8, 25);
        sheet.setColumnView(9, 25);
        sheet.setColumnView(10, 35);
        sheet.setColumnView(11, 25);
        sheet.setColumnView(12, 25);
        sheet.setColumnView(13, 25);
        sheet.setColumnView(14, 25);
        sheet.setColumnView(15, 25);
        sheet.setColumnView(16, 25);
        sheet.setColumnView(17, 25);

        WritableFont wfc = new WritableFont(WritableFont.createFont("黑体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC = new WritableCellFormat(wfc);
        wcfFC.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        // wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        sheet.addCell(new Label(0, 0, title, wcfFC));

        //设置小标题格式
        WritableFont wfc2 = new WritableFont(WritableFont.createFont("黑体"), 11, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC2 = new WritableCellFormat(wfc2);
        wcfFC2.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC2.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC2.setWrap(true);

        //设置普通文字格式
        WritableFont wfc3 = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
        WritableCellFormat wcfFC3 = new WritableCellFormat(wfc3);
        wcfFC3.setAlignment(jxl.format.Alignment.CENTRE); // 设置横向对齐方式
        wcfFC3.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //设置纵向对齐方式
        wcfFC3.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);  //设置单元格边框和边框线条样式
        wcfFC3.setWrap(true);
        sheet.addCell(new Label(0, 0, title, wcfFC));
        sheet.mergeCells(0, 0, count_name.size() - 1, 0);
        for (int i = 0; i < count_name.size(); i++) {
            try {

                sheet.addCell(new Label(i, 1, count_name.get(i).split(",")[0], wcfFC2));
            } catch (Exception ex) {
            }
        }
        ArrayList<CarVO> list = new ArrayList<CarVO>();
        StringBuffer sql = this.getSql();
        Vector v = null;
        try {
            v = dao.getDataSet(ConstUtil.DBNAME, sql.toString());

        } catch (ServletException e) {
            e.printStackTrace();
        }
        if (v != null) {

            for (int i = 1; i < v.size() + 2; i++) {
                sheet.setRowView(i, 400);
            }
            int hang = 2;     //数据应该写在多少行
            for (int i = 0; i < v.size(); i++) {
                ArrayList<String> rowlist = new ArrayList<String>();
                Hashtable hash = (Hashtable) v.get(i);
                CostVO cost = new CostVO();
                cost.setCar_code((String) hash.get("CAR_CODE"));
                cost.setDate((String) hash.get("DATE"));
                cost.setCode((String) hash.get("CODE"));
                cost.setName((String) hash.get("NAME"));
                cost.setRemark((String) hash.get("REMARK"));
                cost.setCost((String) hash.get("COST"));
                rowlist.add(cost.getCar_code());
                rowlist.add(cost.getDate());
                rowlist.add(cost.getCode());
                rowlist.add(cost.getName());
                rowlist.add(cost.getRemark());
                rowlist.add(cost.getCost());
                for (int i2 = 0; i2 < rowlist.size(); i2++) {
                    String thisvalue = rowlist.get(i2);
                    if (thisvalue == null || thisvalue.equals("")) {
                        thisvalue = "";
                    }
                    sheet.addCell(new Label(i2, hang, thisvalue, wcfFC3));
                }
                hang++;
            }
            book.write();
            book.close();
        }
    }

    public InputStream getExcelStream() {
        return excelStream;
    }

    public void setExcelStream(InputStream excelStream) {
        this.excelStream = excelStream;
    }

    public String getExcelTitle() {
        return excelTitle;
    }

    public void setExcelTitle(String excelTitle) {
        this.excelTitle = excelTitle;
    }

}
